# @Project: diablo
# @File: init_agreement_price
# @Time: 2023/7/7 15:37
# @User: Jolly
# @Email: jolly@007vin.com
"""
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃      ☃      ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃ 永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛ 
"""
import datetime
# 小汽配2.0初始化商品定价
# 定价来源：客户提供的excel

import time
import threading
from queue import Queue

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure


class InitAgreementPrice:

    def __init__(self):

        self.file_path = "./hongde_price.xlsx"

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=50,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    # 通过oe号获取库存数据
    def get_stock_by_oe(self, oe, quality):

        if quality == "埃力克斯":
            quality = "艾力克斯"
        elif quality == "拓德":
            quality = "拓德TAUD"
        elif quality == "埃麦佰":
            quality = "埃麦佰"
        elif quality == "耐博德":
            quality = "耐博德"
        elif quality == "瓦士通":
            quality = "瓦士通"

        stocks_identify = []

        if oe:
            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["inventory_data"]
                    conn = db["ChangYuan_meta_stocks"]

                    filter_dict = {
                        'opid': oe,
                        'quality': quality
                    }
                    docs = conn.find(filter_dict, {'identify': 1, 'quality': 1})

                    for doc in docs:
                        identify_dict = {'identify': doc.get("identify", ""),
                                         'quality': doc.get("quality", "")}

                        stocks_identify.append(identify_dict)

            except Exception as e:
                print("获取用户库存数据identify失败 - mongo：", e)
            finally:
                # 将连接放回连接池
                connection.close()

        return stocks_identify

    # 写入数据
    def write_data(self, *args):

        oe = args[0]
        quality = args[1]
        price = args[2]

        # 通过oe号查找库存记录，可能有多个
        stocks_identify = self.get_stock_by_oe(oe, quality)

        # 找到对应多条记录后，遍历获取identify，然后写入到agreement_price表中
        for identify in stocks_identify:

            # 判断品牌是否正确，不相等则跳过
            # if identify.get("quality") != quality:
            #
            #     # print("excel:", oe, " ", quality, " ", price)
            #     # print("mongo_stock:", identify.get("quality"))
            #     continue

            agreement_dict = {
                "agreement_excel_id": 33,
                "identify": identify.get("identify"),
                "agreement_price": price,
                "price_type": "customize",
                "price_value": price,
                "price_desc": f"¥ = {str(price)}",
                "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }

            # 获取2.0MongoDB连接
            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["inventory_data"]
                    conn = db["agreement_excel"]
                    conn.insert_one(dict(agreement_dict))

            except Exception as e:
                print("写入2.0MongoDB数据库单据失败：", e)
            finally:
                # 将连接放回连接池
                connection.close()

    # 获取excel数据，生成器返回
    def get_excel_data(self):

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        previous = next(sheet.iter_rows(), None)
        for row in sheet.iter_rows():
            yield previous, False
            previous = row
        yield previous, True

    # 清空预设定价格协议记录
    def delete_exist_record(self):

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]

                filter_dict = {
                    "price_type": "customize",
                    "agreement_excel_id": 33
                }
                conn.delete_many(filter_dict)

        except Exception as e:
            print("删除已有价格协议数据identify失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 删除重复项
    def delete_one_of_same(self):

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]

                filter_dict = {
                    "price_type": "customize",
                    "agreement_excel_id": 33
                }

                # 查询所有记录，遍历每一个是否有重复项
                docs = conn.find(filter_dict, {'identify': 1})

                for doc in docs:
                    identify = doc.get("identify", "")

                    identify_docs_count = conn.count_documents({"identify": identify, "price_type": "customize", "agreement_excel_id": 33})

                    if identify_docs_count > 1:
                        # 循环删除多余记录
                        for i in range(0, identify_docs_count - 1):
                            conn.delete_one({"identify": identify, "price_type": "customize", "agreement_excel_id": 33})

        except Exception as e:
            print("删除已有价格协议数据identify失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 任务执行
    def task(self):
        q = Queue(maxsize=200)  # 设定最大队列数和线程数

        self.delete_exist_record()

        # self.delete_one_of_same()

        count = 1

        for row, last in self.get_excel_data():
            name = row[0].value
            oe = str(row[1].value)
            quality = row[2].value
            price = row[3].value

            t = threading.Thread(target=self.write_data, args=(oe, quality, price))
            q.put(t)
            if (q.full() == True) or last:
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()

            print("当前写入数：", count, " 零件为：", name, ", 价格为：", price)

            if last:
                break

            count += 1


if __name__ == "__main__":
    InitAgreementPrice()



